drop trigger t_vat_credit_id_trigger;
drop trigger t_vat_user_info_id_trigger;
drop trigger t_user_id_trigger;
drop trigger t_t_vat_type_id_trigger;

drop sequence t_vat_credit_id_seq;
drop sequence t_vat_user_info_id_seq;
drop sequence t_user_id_seq;
drop sequence t_vat_type_id_seq;

drop table t_vat_credit;
drop table t_vat_user_info;
drop table t_user;
drop table t_vat_type;
drop table t_inland_revenue_org;

-- 税务机关
create table t_inland_revenue_org
(
	code					varchar(256)          not null,			--税务机关编码
	name					varchar(1024)		  not null,			--名称
	relation_code			varchar(1024)		  not null,			--下属关系编码
	
	primary key(code)
);
create index t_inland_revenue_org_rc_index on t_inland_revenue_org(relation_code);

-- 税务类型
create table t_vat_type
(
	id						numeric(8,0)			not null,		
	name					varchar(256)			not null,
	sep_max_money			numeric(12,4),							--增值税专用发票限额
	nor_max_money			numeric(12,4),							--普通发票限额
	enabled					numeric(1,0)			not null,
	
	primary key(id)
);

-- 用户
create table t_user
(
	id			numeric(8,0)			not null,
	nsrdzdah	varchar(32)				not null,		--纳税人电子档案号：用于同步		
	name		varchar(64)				not null,		--姓名
	login		varchar(32)				not null,		--登陆账号
	password	varchar(256)			not null,		--密码
	org_code	varchar(256)			not null  references t_inland_revenue_org(code),		--税务机关编码
	
	maintain_credit		numeric(1,0)			not null,	--是否可以有权限删除（或再生效）提交的清单
	maintain_type		numeric(1,0)			not null,	--是否可以维护税务类型
	empower_user		numeric(1,0)			not null,	--是否可以给其它用户赋权
	
	unique(nsrdzdah),
	unique(login),
	primary key(id)
);

-- 纳税人基本信息
create table t_vat_user_info
(
  	id          numeric(8,0)          not null,
  	identify    varchar(100)          not null,			--纳税人识别号
  	name        varchar(400)          not null,			--名称
  	address     varchar(1024),							--地址
  	org_code    varchar(256)          not null  references t_inland_revenue_org(code),			--税务机关编码
  
  	unique(identify),
 	primary key(id)
);
create index t_vat_user_info_org_code_index on t_vat_user_info(org_code);

-- 提交的清单
create table t_vat_credit
(
  	id						numeric(8,0)			not null,
  	creator_id				numeric(8,0)			references t_user(id),							--提交人。若为null，则为纳税人通过接口自已提交
  	submit_time				date					not null,										--提交时间
  	submit_ip				varchar(30)				not null,										--提交IP
  	last_modify_id			numeric(8,0)			references t_user(id),							--最后一次修改记录
  	last_modify_time		date,																	--最后修改时间
  	valid					numeric(1,0)			not null,										--是否有效
  	
  	year					numeric(4,0)			not null,										--所属期：年
  	month					numeric(2,0)			not null,										--所属期：月
  	
  	identify				varchar(100)			not null references t_vat_user_info(identify),	--纳税人识别号
  	
  	apply_vat_credit			numeric(12,4)			not null,					--审请抵减税额
  	this_left_credit			numeric(12,4)			not null,					--审请抵减税额
  	
	primary key(id)
);

-- 清单中的发票
create table t_vat_credit_invoice
(
	vat_credit_id				numeric(8,0)			not null references t_vat_credit(id), -- 关联的清单ID
	
	invoice_category			varchar(100)			not null,					-- 类别：增值税专用发票、通用手工发票、定额发票、其它发票
	
	invoice_code				varchar(100),										--发票代码
  	invoice_num					varchar(100),										--发票号码
  	invoice_type_id				numeric(8,0)			not null,					--项目内容
  	invoice_money				numeric(12,4)			not null,					--价税合计金额
  	invoice_date				date					not null,					--日期
  	place						numeric(8,0)			not null
);

-- 序列
create sequence t_vat_type_id_seq increment by 1 start with 1 nomaxvalue nocycle nocache;
create sequence t_user_id_seq increment by 1 start with 1 nomaxvalue nocycle nocache;
create sequence t_vat_user_info_id_seq increment by 1 start with 1 nomaxvalue nocycle nocache;
create sequence t_vat_credit_id_seq increment by 1 start with 1 nomaxvalue nocycle nocache;

-- 触发器
create or replace trigger t_vat_type_id_trigger before insert on t_vat_type for each row when (new.id is null)
begin
  select t_vat_type_id_seq.nextval into :new.id from dual;
end;

create or replace trigger t_user_id_trigger before insert on t_user for each row when (new.id is null)
begin
  select t_user_id_seq.nextval into :new.id from dual;
end;

create or replace trigger t_vat_user_info_id_trigger before insert on t_vat_user_info for each row when (new.id is null)
begin
  select t_vat_user_info_id_seq.nextval into :new.id from dual;
end;

create or replace trigger t_vat_credit_id_trigger before insert on t_vat_credit for each row when (new.id is null)
begin
  select t_vat_credit_id_seq.nextval into :new.id from dual;
end;

-- 函数
create or replace function get_generated_id(table_name in varchar) return number is
  s varchar(255);
  n number;
begin
  s := 'select ' || table_name || '_id_seq.currval from dual';
  execute immediate s into n;
  return (n);
end;